using System.Data.SqlClient;
using System.Data;
using System;
namespace StillVoiceCommon
{
    public static class sqlDB
    {
        internal static SqlConnection DBConn = new SqlConnection();
        //private SqlConnection DBConnERP = new SqlConnection(); 
        //private SqlConnection DBConnEstimate = new SqlConnection(); 
        internal static SqlConnection DBConnExtra = new SqlConnection();
        internal static SqlConnection getDBConn(string WhichDB)
        {
            SqlConnection functionReturnValue = null;
            if (WhichDB == "ERP")
            {
                //if (DBConnERP.State != ConnectionState.Open) { 
                //    DBConnERP = new SqlConnection(getDBConnStringERP); 
                //    DBConnERP.Open(); 
                //    SqlCommand cmd = new SqlCommand("set dateformat ymd", DBConnERP); 
                //    cmd.ExecuteNonQuery(); 
                //} 
                //functionReturnValue = DBConnERP; 
            }
            else if (WhichDB == "Estimate")
            {
                //if (DBConnEstimate.State != ConnectionState.Open) { 
                //    DBConnEstimate = new SqlConnection(getDBConnStringEstimate); 
                //    DBConnEstimate.Open(); 
                //    SqlCommand cmd = new SqlCommand("set dateformat ymd", DBConnEstimate); 
                //    cmd.ExecuteNonQuery(); 
                //} 
                //functionReturnValue = DBConnEstimate; 
            }
            else
            {
                //Data 
                if (DBConn.State != ConnectionState.Open)
                {
                    DBConn = new SqlConnection(getDBConnString());
                    DBConn.Open();
                    SqlCommand cmd = new SqlCommand("set dateformat ymd", DBConn);
                    cmd.ExecuteNonQuery();
                }
                functionReturnValue = DBConn;
            }
            return functionReturnValue;
        }
        internal static SqlConnection getDBConnExtra(string connString)
        {
            SqlConnection functionReturnValue = null;
            if (DBConnExtra.Database == extractDBName(connString) && DBConnExtra.State == ConnectionState.Open)
            {
                functionReturnValue = DBConnExtra;
                //there's already a connection open to this database 
            }
            else
            {
                DBConnExtra = new SqlConnection(connString);
                DBConnExtra.Open();
                SqlCommand cmd = new SqlCommand("set dateformat ymd", DBConnExtra);
                cmd.ExecuteNonQuery();
                functionReturnValue = DBConnExtra;
            }
            return functionReturnValue;
        }
        internal static string extractDBName(object connString)
        {
            string functionReturnValue = null;
            //Dim substr As String 
            functionReturnValue = "";
            int pos;
            string[] arr;
            string[] arr1;
            arr = connString.ToString().Split(';');
            for (pos = 0; pos <= arr.GetUpperBound(0); pos++)
            {
                arr1 = arr[pos].Split('=');
                if (arr1[0].ToString().ToLower() == "initial catalog")
                {
                    functionReturnValue = arr1[1];
                    break;
                }
            }
            return functionReturnValue;
        }
        public static string getDBConnString()
        {
            //Return System.Configuration.ConfigurationManager.AppSettings("ConnectionString") 
            //return System.Configuration.ConfigurationManager.AppSettings["ConnectionString"];
            return TN5Y.Utilities.DBConnectionString;

        }
        //public string getDBConnStringERP() 
        //{ 
        //    return System.Configuration.ConfigurationManager.AppSettings["ConnectionStringERP"]; 
        //} 
        //public string getDBConnStringEstimate() 
        //{ 
        //    return System.Configuration.ConfigurationManager.AppSettings["ConnectionStringEstimate"]; 
        //} 
        public static SqlDataAdapter getDA(string sql, string WhichDB)
        {
            SqlCommand cmd = new SqlCommand(sql, getDBConn(WhichDB));
            return new SqlDataAdapter(cmd);
        }

        public static SqlDataAdapter getDA(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql, getDBConn("Data"));
            return new SqlDataAdapter(cmd);
        }
        public static SqlDataReader getDR(string sql, string WhichDB)
        {
            SqlCommand cmd = new SqlCommand(sql, getDBConn(WhichDB));
            return cmd.ExecuteReader();
        }
        public static SqlDataReader getDR(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql, getDBConn("Data"));
            return cmd.ExecuteReader();
        }

        public static DataSet getDS(string sql)
        {
            return getDataSet(sql, "Data");
        }
        public static DataSet getDS(string sql, string WhichDB)
        {
            return getDataSet(sql, WhichDB);
        }
        internal static DataSet getDataSet(string sql, string WhichDB)
        {
            DataSet functionReturnValue = null;
            functionReturnValue = new DataSet();
            getDA(sql, WhichDB).Fill(functionReturnValue);
            return functionReturnValue;
        }
        internal static DataSet getDataSet(string sql)
        {
            DataSet functionReturnValue = null;
            functionReturnValue = new DataSet();
            getDA(sql, "Data").Fill(functionReturnValue);
            return functionReturnValue;
        }
        //public bool execSQL(string sql) 
        //{
        //    return executeSQL(sql, "Data");
        //}

        public static bool execSQL(string sql)
        {
            bool retval;
            try
            {

                SqlCommand cmd = new SqlCommand(sql, getDBConn("Data"));
                cmd.ExecuteNonQuery();
                retval = true;
            }
            catch 
            {
                retval = false;
            }
            return retval;
        }

        public static bool execSQL(string sql, string WhichDB)
        {
            bool retval;
            try
            {
                SqlCommand cmd = new SqlCommand(sql, getDBConn(WhichDB));
                cmd.ExecuteNonQuery();
                retval = true;
            }
            catch 
            {
                retval = false;
            }
            return retval;
        }

        public static string Lookup(string tableName, string keyName, int keyVal, string DescriptionName)
        {
            string ret = "";
            SqlDataReader dr;
            dr = getDR("select " + DescriptionName + " from " + tableName + " where " + keyName + " = " + keyVal);
            if (dr.Read())
                ret = dr[0].ToString();

            dr.Close();
            return ret;


        }

        public static string Lookup(string tableName, string keyName, string keyVal, string DescriptionName)
        {

            string ret = "";
            SqlDataReader dr;
            dr = getDR("select " + DescriptionName + " from " + tableName + " where " + keyName + " = '" + keyVal + "'");
            if (dr.Read())
                ret = dr[0].ToString();

            dr.Close();
            return ret;
        }


    }

}